﻿

alter proc sp_Thongtindocgia @maDG int
as
begin
	if(exists( select * from NguoiLon where ma_docgia=@maDG))
		select DocGia.ma_docgia,ho+' '+tenlot+' '+ten as HoTen,DocGia.NgaySinh
		from DocGia,NguoiLon 
		where DocGia.ma_docgia=@maDG and DocGia.ma_docgia=NguoiLon.ma_docgia
		
	if(exists( select * from TreEm where ma_docgia=@maDG))
		select DocGia.ma_docgia,ho+' '+tenlot+' '+ten as HoTen,DocGia.NgaySinh,TreEm.ma_docgia_nguoilon
		from DocGia,TreEm 
		where DocGia.ma_docgia=@maDG and DocGia.ma_docgia=TreEm.ma_docgia
end

exec sp_Thongtindocgia 1
----------4.2--------------------------------------------
alter proc sp_ThongtinDausach @isbn int
as begin
				declare @slsachchuamuon int
				set @slsachchuamuon = ( select count(ma_cuonsach)
				 											   from CuonSach
				 											   where isbn = @isbn and ma_cuonsach not in (select ma_cuonsach			 																																 from Muon
				 																																 where isbn = @isbn))	
			declare @c cursor ,@madausach int ,@tuasach nvarchar(30),@tacgia nvarchar(30)
			set @c = cursor for (select d.isbn,t.TuaSach,t.tacgia
												from DauSach d,TuaSach t
												where d.ma_tuasach = t.ma_tuasach)
			open @c
			fetch next from @c into @madausach,@tuasach,@tacgia
			while @@fetch_status =0
			begin
						print cast((@madausach) as char(3)) + @tuasach + @tacgia +' '+ cast ((@slsachchuamuon) as char(3))
						fetch next from @c into @madausach,@tuasach,@tacgia
						break
			end	
			close @c
			deallocate @c																																																				  				 											 				
end

exec sp_ThongtinDausach 3




---------4.3--------
alter proc  sp_ThongtinNguoilonDangmuon 
as
begin 
declare @c cursor
declare @ma int, @ten nvarchar(30),@ho nvarchar(30),@tenlot nvarchar(30),@sonha nvarchar(30),@quan nvarchar(30)
set @c = cursor for select dg.ma_docgia,dg.ten,nl.sonha,nl.quan from DocGia dg, NguoiLon nl where dg.ma_docgia=nl.ma_docgia 
				and dg.ma_docgia in (Select ma_docgia from Muon)
open @c 
fetch next from @c into  @ma ,@ten,@sonha,@quan
while @@fetch_status = 0
	begin
				print cast(@ma as char(4)) + @ten+' ' + @sonha +' '+ @quan
				fetch next from @c into @ma ,@ten,@sonha,@quan
	end
	close @c
	deallocate @c																		 			 
end


exec sp_ThongtinNguoilonDangmuon


--4.4.  Liệt kê những độc giả người lớn đang mượn sách quá hạn: 
create proc sp_ThongtinNguoilonQuahan
as begin
	declare @c cursor ,@ma int ,@ten nvarchar(30)	
	set @c = cursor for select n.ma_docgia,d.ten
										from NguoiLon n , DocGia d
										where n.ma_docgia = d.ma_docgia 
										and n.ma_docgia in (select ma_docgia
																			from Muon
																			)
										and n.ma_docgia not in ( select ma_docgia
																					 from  QuaTrinhMuon
																					)									
	open @c
	fetch next from @c into @ma,@ten
	while @@fetch_status = 0
	begin
				print cast(@ma as char(4)) + @ten
				fetch next from @c into @ma,@ten
	end
	close @c
	deallocate @c																		 			 
end

exec sp_ThongtinNguoilonQuahan

--4.5.  Liệt  kê  những độc  giả người  lớn đang mượn  sách  có  trẻ em cũng đang mượn sách: 
create proc sp_DocGiaCoTreEmMuon 
as begin
	declare @c cursor ,@ma int ,@ten nvarchar(30)	
	set @c = cursor for	select n.ma_docgia,d.ten
										from NguoiLon n , DocGia d ,TreEm t
										where t.ma_docgia = d.ma_docgia and t.ma_docgia_nguoilon = n.ma_docgia 
										and d.ma_docgia in (select ma_docgia
																			from Muon
																			)
	open @c
	fetch next from @c into @ma,@ten
	while @@fetch_status = 0
	begin
				print cast(@ma as char(4)) + @ten
				fetch next from @c into @ma,@ten
	end
	close @c
	deallocate @c																		 			 
end

exec sp_DocGiaCoTreEmMuon 


--4.6.  Cập nhật trạng thái của đầu sách:

create proc sp_CapnhatTrangthaiDausach @isbn int 
as begin
				declare @c cursor,@tinhtrang nvarchar(2),@flag int
				set @c = cursor for select TinhTrang
												   from CuonSach
												   where @isbn = isbn
				set @flag = 0								   
				open @c
				fetch next from @c into @tinhtrang
				while @@fetch_status = 0
				begin
							if @tinhtrang = 'Y'
								set @flag = 1
							fetch next from @c into @tinhtrang	
				end	
				close @c
				deallocate @c
				if @flag = 0
					update DauSach set trangthai = 'N' where isbn =  @isbn
				else
					update DauSach set trangthai= 'Y' where isbn = @isbn										    		
end 

exec sp_CapnhatTrangthaiDausach 4


--4.7.Thêm tựa sách mới:
create proc sp_ThemTuaSach @tuasach nvarchar(20),@tacgia nvarchar(20),@tomtat nvarchar(20)
as begin
	declare @mats int
	set @mats = 1
	while(@mats in (select ma_tuasach
						from TuaSach
						 ))
	begin
			set @mats = @mats +1	
	end	
	declare @duyetma int
	declare @cma cursor
	set @cma= cursor for select ma_tuasach
						from TuaSach
	open @cma
	declare @flag int
	set @flag = 0
	fetch next from @cma into @duyetma
	while @@fetch_status = 0
	begin
			if((@tuasach = (select TuaSach
						  from TuaSach
						  where ma_tuasach = @duyetma ))
				and
			(@tacgia = (select tacgia
						  from TuaSach
						  where ma_tuasach = @duyetma ))
				and
			(@tomtat = (select tomtat
						  from TuaSach
						  where ma_tuasach = @duyetma )	)
			or @tomtat is null			  )
			 begin
						set @flag = 1
			 end 
			fetch next from @cma into @duyetma			 				  		   
	end	
	close @cma
	deallocate @cma
		if(@flag = 0)	
		begin
		insert into TuaSach values (@mats,@tuasach,@tacgia,@tomtat)
		print N'Thêm thành công'	
	end
		else
			print N'Tựa sách đã tồn tại,không thể thêm dử liệu'
													 
end

exec sp_ThemTuaSach N'Last of the Mohicans',N'James Fenimore Cooper','e'

--4.8.Thêm cuốn sách mới:
create proc sp_ThemCuonSach @isbn int

alter proc sp_ThemCuonSach @isbn int  
as begin
	if(@isbn not in(select isbn
					from DauSach)
	  )
	  begin
	  print N'Đầu sách không tồn tại'
	  return
	  end
	 declare @macs int
		set @macs =  (select max( Ma_CuonSach)
							 from CuonSach
							where @isbn= isbn  )
		set @macs = 	@macs  +1					  
	 declare @TinhTrang Nvarchar(5) 
	 set @TinhTrang='Y'
	 insert into CuonSach values (@isbn,@macs,@TinhTrang)
	 update DauSach set trangthai = 'Y' where isbn=@isbn
end

exec sp_ThemCuonSach 3

--4.9.  Thêm độc giả người lớn:
create proc sp_ThemNguoilon @ho nvarchar(10),@tenlot nvarchar(10),@ten nvarchar(10),@ngaysinh datetime,@sonha int,@duong nvarchar(20),@quan nvarchar(10) 
 as begin
				declare @ma int,@tuoi int
				set @ma = (select max(ma_docgia)
									from DocGia)
				set @ma = @ma + 1
				insert into DocGia values (@ma, @ho,@tenlot, @ten, @ngaysinh)
				set @tuoi = datediff(yy,@ngaysinh,getdate())
				if @tuoi < 18
				begin
						print N'không đủ tuổi !!!'
						return
				end
			else
						insert into NguoiLon values (@ma,@sonha,@duong,@quan,null,getdate())											 
 end 
 
exec  sp_ThemNguoilon 'a','b','c','3/3/1991',24,N'Bui thị Xuân','GV'

--4.10. Thêm độc giả trẻ em: 
create proc sp_ThemTreEm  @ho nvarchar(10),@tenlot nvarchar(10),@ten nvarchar(10),@ngaysinh datetime,@madocgia int

alter proc sp_ThemTreEm  @ho nvarchar(10),@tenlot nvarchar(10),@ten nvarchar(10),@ngaysinh datetime,@madocgia int
 as begin
				declare @ma int
				set @ma = (select max(ma_docgia)
									from DocGia)
				set @ma = @ma + 1
				insert into DocGia values (@ma, @ho,@tenlot, @ten, @ngaysinh)
			set  @madocgia = (	select count(t.ma_docgia_nguoilon)
												from  NguoiLon n ,TreEm t
												where t.ma_docgia_nguoilon = n.ma_docgia and n.ma_docgia = @madocgia )
			if @madocgia >=2
			begin
						print N'Đoc giả nay đã vượt số trẻ em bảo lãnh'
						return
			end
			insert into TreEm values (@ma,@madocgia)																									 
 end 
 
exec   sp_ThemTreEm 'a','b','c','3/3/2000',3

--4.11. Xóa độc giả:
alter proc sp_XoaDocGia @madocgia int
as begin
				if(@madocgia not in (select ma_docgia
													   from DocGia ))
				begin
							print N'Không tồn tại độc giả '
							return
				end
				if(@madocgia in (select ma_docgia
													   from Muon ))
				begin
							print N'Không thể xóa độc giả được '
							return
				end
					if(@madocgia in (select ma_docgia
													   from DocGia ))
				begin
							if(@madocgia not in (select ma_docgia_nguoilon
																   from TreEm )
																   and
								@madocgia not in (select ma_docgia
																   from TreEm	   ))
							begin
										delete from NguoiLon where ma_docgia = @madocgia
										delete from QuaTrinhMuon where ma_docgia = @madocgia
										delete from DangKy where ma_docgia = @madocgia
										delete from DocGia where ma_docgia = @madocgia
							end
							else if(@madocgia in (select  ma_docgia_nguoilon
																   from TreEm ))
							begin
										declare @c cursor,@timdocgiatreem int
										set @c = cursor for (select ma_docgia
																			from TreEm
																			where @madocgia = ma_docgia_nguoilon		)
										open @c
										fetch next from @c into @timdocgiatreem
										while @@fetch_status=0
										begin
													delete from TreEm where ma_docgia = @timdocgiatreem
													fetch next from @c into @timdocgiatreem
										end	
										close @c
										deallocate @c																												
										delete from NguoiLon where ma_docgia = @madocgia
										delete from QuaTrinhMuon where ma_docgia = @madocgia
										delete from DangKy where ma_docgia = @madocgia
										delete from DocGia where ma_docgia = @madocgia
							end
							else 
							begin
										delete from TreEm where ma_docgia = @madocgia
										delete from QuaTrinhMuon where ma_docgia = @madocgia
										delete from DangKy where ma_docgia = @madocgia
										delete from DocGia where ma_docgia = @madocgia								
							end									   													   
				end																	   	
end

exec sp_XoaDocGia 108


----4.12---
alter PROC sp_MuonSach
	@isbn int,@masach int ,@madg int
AS
BEGIN
	
	if(exists(select * from muon where ma_docgia=@madg ANd ma_cuonsach=@masach))	
		begin
				print N'Lỗi'
				return
		end
	else
		begin
		declare @ssnl int
		declare @ssnl2 int 
		declare @manbl int
		declare @treem int
			if(exists(select * from NguoiLon where ma_docgia = @madg))
			begin
				
				set @ssnl = (select count(*) from Muon where ma_docgia = @madg)
				declare @soluongbaolanh int
				set @soluongbaolanh = (select count(*) from Muon m ,TreEm t where m.ma_docgia = t.ma_docgia AND t.ma_docgia_nguoilon = @madg)
				declare @tong int 
				set @tong  = (@ssnl + @soluongbaolanh)
				if(@tong = 5)
				begin
					print N'So luong sach da bang 5'
					return
				end
			end
			if(exists(select * from TreEm where ma_docgia = @madg))
			begin
				
				set @treem = (select count(*) from Muon m ,TreEm t where m.ma_docgia = t.ma_docgia)
				if(@treem < 1)
				begin
					declare @ss int
					set @ss = (select count(*) from Muon m ,TreEm t where m.ma_docgia = t.ma_docgia_nguoilon AND t.ma_docgia = @madg)
					if(@ss = 5)
					begin
						print N'So luong sach nguoi bao lanh bang 5'
						return 
					end
					
				end
				if( @treem = 1)
				begin
					print N'So luong sach tre em bang 1'
						return 
				end
					
			end
			if( exists(SELECT * FROM CuonSach WHERE Ma_CuonSach = @masach AND isbn = @isbn AND TinhTrang = 'Y'))
			begin 
				select * from DauSach
				insert into Muon values(@isbn,@masach,@madg,getdate(),DATEADD(dd,7,getdate()))
				update CuonSach set TinhTrang = 'N' WHERE Ma_CuonSach = @masach AND isbn = @isbn
				if(not exists(SELECT * FROM CuonSach WHERE Ma_CuonSach = @masach AND isbn = @isbn AND TinhTrang = 'Y'))
				begin
					update DauSach set trangthai = 'N' WHERE isbn = @isbn AND ma_tuasach = @masach
					print N'Mượn sách thành công'
				end
			end
			else
			begin
				print N'Chờ đi'
				INSERT into DangKy values(@isbn,@madg,getdate(),NULL)
			end
			
		
		end

END

exec sp_MuonSach 1,3,93
select * from TreEm
select * from NguoiLon
select * from Muon where ma_docgia = 92
select * from muon where ma_docgia=93 ANd ma_cuonsach=1
select * from DauSach
select * from cuonSach
insert into Muon values(1,1,2,getdate(),DATEADD(dd,7,getdate()))

---------4.13-----------


create procedure sp_TraSach
	@ISBN smallint,
	@MaDocGia smallint
as
begin
	Declare @NgayTra smalldatetime
	select @NgayTra = ngay_hethan  from Muon where isbn = @ISBN and ma_docgia = @MaDocGia

	Declare @Tre smallint
	set @Tre = DATEDIFF(DD,@NgayTra, getdate())

	if( @Tre >0)
	begin
		print 'Tre ' + cast(@Tre as nvarchar(10)) + ' Ngay (Ngay Het Han: ' + convert(nchar(10),@NgayTra,103) + ')'
		print 'Tien Phat: ' + cast(@Tre * 1000 as nchar(10))
	end

	--Insert bang Qua Trinh Muon
	insert into QuaTrinhMuon (isbn, ma_cuonsach, ngayGio_muon, ma_docgia, ngay_hethan, ngayGio_tra)
		select isbn, ma_cuonsach, ngayGio_muon, ma_docgia, ngay_hethan, getdate()
		from Muon 
		where isbn = @ISBN and ma_docgia = @MaDocGia

	--Delete Bang Muon
	Delete Muon where isbn = @ISBN and ma_docgia = @MaDocGia
end
 